#coding=utf8
import sqlite3
import os
# test sqlite by michael

#global var

DB_FILE_PATH = 'robotDB.db'
TABLE_NAME = 'User'
SHOW_SQL = True

def get_conn(path):
    conn = sqlite3.connect(DB_FILE_PATH)
    print "successfully open database..."
    return conn

def get_cursor(conn):
    '''获取数据库的游标对象'''
    if conn is not None:
        return conn.cursor()
    else:
        return get_conn('').cursor()

#删除表操作
def drop_table(conn, table):
    if table is not None and table != '':
        sql = 'DROP TABLE IF EXISTS ' + table
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu = get_cursor(conn)
        cu.execute(sql)
        conn.commit()
        print('删除数据库表[{}]成功!'.format(table))
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def create_table(conn, sql):
    '''创建数据库表'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu.execute(sql)
        conn.commit()
        print('创建数据库表成功！')
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def close_all(conn, cu):
    try:
        if cu is not None:
            cu.close()
    finally:
        if cu is not None:
            cu.close()

def save(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}], 参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def fetchall(conn, sql):
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu.execute(sql)
        r = cu.fetchall()
        if len(r) > 0:
            for e in range(len(r)):
                print(r[e])
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def fetchone(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            d = (data,)
            cu = get_cursor(conn)
            if SHOW_SQL:
                print('执行sql:[{}], 参数:[{}]'.format(sql, data))
            cu .execute(sql, d)
            r = cu.fetchone()
            print(r)
            return r
        else:
            print('the [{}] is  equal None!'.format(data))
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def update(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            if SHOW_SQL:
                print('执行sql:[{}], 参数:[{}]'.format(sql, data))
            cu.execute(sql, data)
            conn.commit()
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))

def delete(conn, sql, data):
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor()
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}], 参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))



def create_table_test():
    '''创建测试表'''
    print('开始创建测试表')
    create_table_sql =  '''CREATE TABLE IF NOT EXISTS 'user' (
                            'id' int(11) NOT NULL,
                            'name' varchar(20) NOT NULL,
                            'messageCount' int(11) NOT NUll,
                            PRIMARY KEY ('id')
                        )'''
    conn = get_conn('')
    create_table(conn, create_table_sql)

'''
def main():
    print('主函数启动...')
    conn = get_conn('')
    cursor = get_cursor(conn)
    create_table_test()

main()'''
